﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System.Data;

using BySoft.Server.Common.Entity;
using BySoft.Server.Common.Schema;
using BySoft.Server.Consts;
using BySoft.Server.Common.Enum;
using System.Data.OleDb;
using BySoft.Server.Common;

namespace BySoft.Server.DataAccess
{
    public partial class ClsCommDA
    {

        private static object lockobj = new object();

        #region 更新单据号OK
        public static int GetMaxSequenceID(string sequenceName, DataAccessBroker broker)
        {
            int iStartValue = 1;

            lock (lockobj)
            {
                int maxId = 0;
                string sqlStr = "SELECT MaxID FROM SYS_GenerateSequenceID WHERE SequenceName=@SequenceName";
                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                dpc.AddWithValue("@SequenceName", sequenceName);

                object obj = broker.ExecuteSQLScalar(sqlStr, dpc);

                if (null != obj)
                {
                    int.TryParse(obj.ToString(), out maxId);
                }
                else
                {
                    maxId = iStartValue;
                }

                return maxId;
            }
        }

        public static int GetMaxSequenceID(string sequenceName)
        {
            int iStartValue = 1;

            lock (lockobj)
            {
                using (DataAccessBroker broker = DataAccessFactory.Instance())
                {
                    int maxId = 0;
                    string sqlStr = "SELECT MaxID FROM SYS_GenerateSequenceID WHERE SequenceName=@SequenceName";
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    dpc.AddWithValue("@SequenceName", sequenceName);

                    object obj = broker.ExecuteSQLScalar(sqlStr, dpc);

                    if (null != obj)
                    {
                        int.TryParse(obj.ToString(), out maxId);
                    }
                    else
                    {
                        maxId = iStartValue;
                    }

                    return maxId;
                }
            }
        }

        public static void SetMaxSequenceID(string sequenceName, DataAccessBroker broker)
        {
            int iStartValue = 1;
            lock (lockobj)
            {
                string sqlStr = "SELECT MaxID FROM SYS_GenerateSequenceID WHERE SequenceName=@SequenceName";
                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                dpc.AddWithValue("@SequenceName", sequenceName);

                object obj = broker.ExecuteSQLScalar(sqlStr, dpc);

                if (null != obj)
                {
                    sqlStr = "UPDATE SYS_GenerateSequenceID SET MaxID =MaxID+" + iStartValue + " WHERE SequenceName=@SequenceName";
                }
                else
                {
                    sqlStr = "INSERT SYS_GenerateSequenceID(SequenceName, MaxID) VALUES(@SequenceName,@MaxID)";
                    dpc.AddWithValue("@MaxID", iStartValue + 1);
                }
                broker.ExecuteSQL(sqlStr, dpc);
            }
        }

        public static void SetMaxSequenceID(string sequenceName)
        {
            int iStartValue = 1;
            lock (lockobj)
            {
                using (DataAccessBroker broker = DataAccessFactory.Instance())
                {
                    string sqlStr = "SELECT MaxID FROM SYS_GenerateSequenceID WHERE SequenceName=@SequenceName";
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    dpc.AddWithValue("@SequenceName", sequenceName);

                    object obj = broker.ExecuteSQLScalar(sqlStr, dpc);

                    if (null != obj)
                    {
                        sqlStr = "UPDATE SYS_GenerateSequenceID SET MaxID =MaxID+" + iStartValue + " WHERE SequenceName=@SequenceName";
                    }
                    else
                    {
                        sqlStr = "INSERT SYS_GenerateSequenceID(SequenceName, MaxID) VALUES(@SequenceName,@MaxID)";
                        dpc.AddWithValue("@MaxID", iStartValue + 1);
                    }

                    broker.ExecuteSQL(sqlStr, dpc);
                }
            }
        }

        public static int GetAutalMaxSequenceID(string sequenceName)
        {
            int iStartValue = 1;

            //lock (lockobj)
            //{
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                int maxId = 0;
                string sqlStr = "SELECT MaxID FROM SYS_GenerateSequenceID WHERE SequenceName=@SequenceName";
                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                dpc.AddWithValue("@SequenceName", sequenceName);

                object obj = broker.ExecuteSQLScalar(sqlStr, dpc);

                if (null != obj)
                {
                    int.TryParse(obj.ToString(), out maxId);
                    sqlStr = "UPDATE SYS_GenerateSequenceID SET MaxID =MaxID+" + iStartValue + " WHERE SequenceName=@SequenceName";
                }
                else
                {
                    maxId = iStartValue;
                    sqlStr = "INSERT SYS_GenerateSequenceID(SequenceName, MaxID) VALUES(@SequenceName,@MaxID)";
                    dpc.AddWithValue("@MaxID", iStartValue + 1);
                }
                broker.ExecuteSQL(sqlStr, dpc);

                return maxId;
            }
            //}
        }

        public static int GetAutalMaxSequenceID(string sequenceName, DataAccessBroker broker)
        {
            int iStartValue = 1;

            lock (lockobj)
            {
                int maxId = 0;
                string sqlStr = "SELECT MaxID FROM SYS_GenerateSequenceID WHERE SequenceName=@SequenceName";
                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                dpc.AddWithValue("@SequenceName", sequenceName);

                object obj = broker.ExecuteSQLScalar(sqlStr, dpc);

                if (null != obj)
                {
                    int.TryParse(obj.ToString(), out maxId);
                    maxId += iStartValue;
                    sqlStr = "UPDATE SYS_GenerateSequenceID SET MaxID =MaxID+" + iStartValue + " WHERE SequenceName=@SequenceName";
                }
                else
                {
                    maxId = iStartValue;
                    sqlStr = "INSERT SYS_GenerateSequenceID(SequenceName, MaxID) VALUES(@SequenceName,@MaxID)";
                    dpc.AddWithValue("@MaxID", iStartValue + 1);
                }
                broker.ExecuteSQL(sqlStr, dpc);

                return maxId;
            }
        }
        #endregion

        public static bool CheckExistNoMainKey(string strTbl, string strMainKey, string strKeyValue, Dictionary<string, string> dc)
        {
            string strcmd = @"SELECT COUNT(" + strMainKey + ") FROM " + strTbl + " WHERE 1=1 and Status<>'0'";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(strKeyValue))
            {
                strcmd += "and " + strMainKey + "<>@" + strMainKey;
                dpc.AddWithValue("@" + strMainKey, strKeyValue);
            }

            if (dc != null && dc.Count > 0)
            {
                foreach (KeyValuePair<string, string> item in dc)
                {
                    strcmd += string.Format(" and {0}=@{0}", item.Key);
                    dpc.AddWithValue("@" + item.Key, item.Value);
                }
            }
            return ExeBoolean(strcmd, dpc);
        }

        public static bool CheckExistNoMainKey(string strTbl, string strMainKey, string strKeyValue, string strNameKey, string strNameValue)
        {
            string strcmd = string.Format(@"SELECT COUNT({0}) FROM {1} WHERE 1=1 and Status<>'0' and {2}='{3}'", strMainKey, strTbl, strNameKey, strNameValue);
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(strKeyValue))
            {
                strcmd += "and " + strMainKey + "<>@" + strMainKey;
                dpc.AddWithValue("@" + strMainKey, strKeyValue);
            }
            return ExeBoolean(strcmd, dpc);
        }

        public static DataTable SelectCheckOrder(string UserID, string WorkGroupName, string BillID)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    string strcmd = "select * from V_CheckOrder a where CheckBillType='" + BillID + "'";
                    if (UserID != "")
                    {
                        strcmd += " and a.UserId='" + UserID + "'";
                    }
                    if (WorkGroupName != "")
                    {
                        strcmd += " and a.WorkGroupName='" + WorkGroupName + "'";
                    }
                    strcmd += " order by a.CheckIndex";
                    return broker.FillSQLDataSet(strcmd).Tables[0];
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectProcessQuantity(string ProductInfoMcod)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    string strcmd = "select * from V_ProcessQuantity a where a.ProductInfoMCod='" + ProductInfoMcod + "'";
                    return broker.FillSQLDataSet(strcmd).Tables[0];
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectGetField(string tableName)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    //for  2005
                    string strcmd = @"SELECT A.[object_id],A.[name],B.[name] AS ColunmName,E.[name] AS typename,CASE e.[name] WHEN 'varchar' THEN  b.max_length WHEN 'nvarchar' THEN  b.max_length else B.precision END AS size,
B.scale
FROM sys.tables A
INNER JOIN sys.[columns] B ON A.[object_id]=B.[object_id]
LEFT JOIN  sys.types E on B.system_type_id=E.user_type_id
WHERE A.[name]='" + tableName + "'";

                    //for 2000
                    //                    string strcmd = @"SELECT 
                    //distinct A.ID as 'object_id', A.Name as 'name', 
                    //B.Column_Name AS 'ColunmName', B.DATA_TYPE typename, (CASE  B.DATA_TYPE WHEN 'DATETIME' THEN 23 
                    //WHEN 'varchar' THEN B.CHARACTER_MAXIMUM_LENGTH 
                    //WHEN 'INT' THEN B.NUMERIC_PRECISION WHEN 'DECIMAL' THEN B.NUMERIC_PRECISION END) AS size,
                    //(CASE   B.DATA_TYPE WHEN  'DATETIME' THEN 3 ELSE  ISNULL( B.Numeric_Scale,0) END)AS scale
                    //FROM sysobjects AS A INNER JOIN information_schema.columns AS B ON A.Name = 'BS_Card' AND b.TABLE_NAME='" + tableName + "'";

                    return broker.FillSQLDataSet(strcmd).Tables[0];
                }
                catch
                {
                    throw;
                }
            }
        }

        

        public static DataTable CheckUsed(string strCode, string strSQL)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                string strcmd = @"SELECT t.tablename FROM (" + strSQL + ") t WHERE t.NUM>0";
                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                dpc.AddWithValue("@strCode", strCode);

                try
                {
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable ExcelDataSource(string filepath, string sheetname)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "$]", strConn);
            DataSet ds = new DataSet();
            try
            {
                oada.Fill(ds);
                return ds.Tables[0];
            }
            catch
            {
            }
            return null;
        }

        public static bool CheckExist(string strTbl, string strMainKey, string strKeyValue,bool bStatus)
        {
            string strcmd = @"SELECT COUNT(" + strMainKey + ") FROM " + strTbl + " WHERE 1=1 and " + strMainKey + "=@" + strMainKey;
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@" + strMainKey, strKeyValue);
            if (bStatus)
            {
                strcmd += " and Status<>'0'";
            }

            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (Convert.ToInt32(obj.ToString().Trim()) > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch
                {
                    throw;
                }
            }
        }

        public static string GetFieldValue(string strTbl, string strMainKey, string strKeyValue, string strField)
        {
            string strcmd = @"SELECT " + strField + " FROM " + strTbl + " WHERE 1=1 and " + strMainKey + "=@" + strMainKey;
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@" + strMainKey, strKeyValue);

            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (obj != null)
                    {
                        return obj.ToString().Trim();
                    }
                    else
                    {
                        return "";
                    }
                }
                catch
                {
                    throw;
                }
            }
        }

        public static bool CheckNameWithoutID(string strTbl, string strMainKey, string strKeyValue, string strNameKey, string strNameValue, bool bStatus)
        {
            string strcmd = @"SELECT COUNT(" + strMainKey + ") FROM " + strTbl + " WHERE 1=1 and " + strMainKey + "<>@strMainKey and " + strNameKey + "=@strNameKey";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@strMainKey", strKeyValue);
            dpc.AddWithValue("@strNameKey", strNameValue);
            if (bStatus)
            {
                strcmd += " and Status='1'";
            }
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (Convert.ToInt32(obj.ToString().Trim()) > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch
                {
                    throw;
                }
            }
        }

        public static void UpdateStatus(string strTbl, string strMainKey, string strKeyValue, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"UPDATE " + strTbl + " SET Status = '0' WHERE " + strMainKey + "=@" + strMainKey;
            dpc.AddWithValue("@" + strMainKey, strKeyValue);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static void UpdateInCheck(string strTbl, string strMainKey, string strKeyValue, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"UPDATE " + strTbl + " SET Status = '2' WHERE " + strMainKey + "=@" + strMainKey;
            dpc.AddWithValue("@" + strMainKey, strKeyValue);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static void UpdateStatus(string strTbl, string strMainKey, string strKeyValue,string strStatus, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"UPDATE " + strTbl + " SET Status = @Status WHERE " + strMainKey + "=@" + strMainKey;
            dpc.AddWithValue("@" + strMainKey, strKeyValue);
            dpc.AddWithValue("@Status", strStatus);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static void DelCheckRecord(string strSign, string strID, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"DELETE FROM SYS_BillCheckRecords WHERE BillType=@BillType and BillID=@BillID";
            dpc.AddWithValue("@BillType", strSign);
            dpc.AddWithValue("@BillID", strID);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static void Del(string strTbl, string strMainKey, string strKeyValue, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"DELETE FROM " + strTbl + " WHERE " + strMainKey + "=@" + strMainKey;
            dpc.AddWithValue("@" + strMainKey, strKeyValue);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static void DelAudit(string strTbl, string strKeyValue, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"DELETE FROM SYS_FormAuditRecord
WHERE BillType=@BillType AND BillID=@BillID";
            dpc.AddWithValue("@BillType", strTbl);
            dpc.AddWithValue("@BillID", strKeyValue);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static void DelAuditBill(string strTbl, string strKeyValue, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"DELETE FROM SYS_FormAuditBill
WHERE BillType=@BillType AND BillID=@BillID";
            dpc.AddWithValue("@BillType", strTbl);
            dpc.AddWithValue("@BillID", strKeyValue);
            broker.ExecuteSQL(strcmd, dpc);
        }


        public static void MoveTop(string strTbl, string strOrderField, string strMainKey, string strMainKeyValue, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"UPDATE " + strTbl + " SET " + strOrderField + " = 1 WHERE " + strOrderField + " IS NULL;UPDATE " + strTbl + " SET " + strOrderField + " = " + strOrderField + "+1;UPDATE " + strTbl + " SET " + strOrderField + " =1 WHERE " + strMainKey + "=@" + strMainKey;
            dpc.AddWithValue("@" + strMainKey, strMainKeyValue);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static string SelectMainKeyWithOtherOrder(string strType, string strTbl, string strMainKey, string strOrderField, string strOrderValue, string strWhere2, string strWhere2Value, string strWhere3, string strWhere3Value, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"SELECT  TOP 1 a." + strMainKey + " FROM " + strTbl + " a WHERE a.[Status]='1'";
            if (!string.IsNullOrEmpty(strWhere2Value))
            {
                strcmd += " and " + strWhere2 + "=@" + strWhere2;
                dpc.AddWithValue("@" + strWhere2, strWhere2Value);
            }
            if (!string.IsNullOrEmpty(strWhere3Value))
            {
                strcmd += strWhere3;
            }
            if (strType.Equals(BaseEnum.OrderType.Pre.ToString()))
            {
                strcmd += @" AND a." + strOrderField + "<@" + strOrderField + " ORDER BY a." + strOrderField + " DESC";
            }
            else
            {
                strcmd += @" AND a." + strOrderField + ">@" + strOrderField + " ORDER BY a." + strOrderField;
            }
            dpc.AddWithValue("@" + strOrderField, strOrderValue);

            object obj = broker.ExecuteSQLScalar(strcmd, dpc);
            if (obj != null)
            {
                return obj.ToString().Trim();
            }
            else
            {
                return "";
            }
        }

        public static void SetOrderNo(string strTbl, string strMainKey, string strOriCode, string strOtherCode, string strOrderField, string strOrderValue, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"UPDATE " + strTbl + " SET " + strOrderField + " = (SELECT TOP 1 aa." + strOrderField + " FROM " + strTbl + " aa WHERE aa." + strMainKey + "=@strOtherCode) WHERE " + strMainKey + "=@strCode;UPDATE " + strTbl + " SET " + strOrderField + " = @strOrderNo WHERE " + strMainKey + "=@strOtherCode;";
            dpc.AddWithValue("@strOtherCode", strOtherCode);
            dpc.AddWithValue("@strCode", strOriCode);
            dpc.AddWithValue("@strOrderNo", strOrderValue);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static void FillOrderNoWithNull(string strTbl, string strMainKey, string strOrderField, DataAccessBroker broker)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("IF EXISTS(SELECT " + strMainKey + " FROM " + strTbl);
            sb.AppendLine("          WHERE " + strOrderField + " IS NULL");
            sb.AppendLine("          OR EXISTS(SELECT aa." + strOrderField);
            sb.AppendLine("                     FROM " + strTbl + " aa");
            sb.AppendLine("                    WHERE aa." + strOrderField + "=" + strTbl + "." + strOrderField);
            sb.AppendLine("                     GROUP BY aa." + strOrderField);
            sb.AppendLine("                     HAVING COUNT(aa." + strMainKey + ")>1))");
            sb.AppendLine("BEGIN");
            sb.AppendLine("DECLARE CUR_Stock CURSOR FOR");
            sb.AppendLine("	SELECT a." + strMainKey + ", a." + strOrderField);
            sb.AppendLine("  FROM " + strTbl + " a WHERE a.[Status]='1'");
            sb.AppendLine("ORDER BY a." + strOrderField);
            sb.AppendLine("OPEN CUR_Stock");
            sb.AppendLine("DECLARE @" + strMainKey + " INT");
            sb.AppendLine("DECLARE @" + strOrderField + " INT");
            sb.AppendLine("DECLARE @c INT");
            sb.AppendLine("SET @c=1");
            sb.AppendLine("FETCH NEXT FROM CUR_Stock");
            sb.AppendLine("	INTO @" + strMainKey + ",@" + strOrderField);
            sb.AppendLine("WHILE (@@FETCH_STATUS = 0)");
            sb.AppendLine("BEGIN");
            sb.AppendLine("	UPDATE " + strTbl + " SET " + strOrderField + " = @c WHERE " + strMainKey + "=@" + strMainKey);
            sb.AppendLine("	SET @c=@c+1");
            sb.AppendLine("	FETCH NEXT FROM CUR_Stock");
            sb.AppendLine("		INTO @" + strMainKey + ",@" + strOrderField);
            sb.AppendLine("END");
            sb.AppendLine("CLOSE CUR_Stock");
            sb.AppendLine("DEALLOCATE CUR_Stock");
            sb.AppendLine("END");
            broker.FillSQLDataSet(sb.ToString());
        }

        public static bool IsExistCheckUser(string strCheckType, string strUserID)
        {
            string strcmd = @"SELECT COUNT(A.BillCheckID) FROM SYS_BillCheck A
LEFT JOIN SYS_BillCheckDetail B ON B.BillCheckID=A.BillCheckID
WHERE A.IsCheck='1' 
AND A.BillCode=@BillCode
AND B.CheckUser=@CheckUser";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillCode", strCheckType);
            dpc.AddWithValue("@CheckUser", strUserID);

            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    int ic = Convert.ToInt32(broker.ExecuteSQLScalar(strcmd, dpc));
                    if (ic>0)
                    {
                        b = true;
                    }
                    else
                    {
                        b = false;
                    }
                }
                catch
                {
                    b = false;
                    throw;
                }
            }
            return b;
        }

        public static bool IsUserHaveCheck(string strCheckType, string strMainKey, string strUserID)
        {
            string strcmd = @"SELECT COUNT(BillCheckRecordsID) FROM SYS_BillCheckRecords
WHERE BillType=@BillType
AND BillID=@BillID
AND CheckUser=@CheckUser";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillType", strCheckType);
            dpc.AddWithValue("@BillID", strMainKey);
            dpc.AddWithValue("@CheckUser", strUserID);

            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    int ic = Convert.ToInt32(broker.ExecuteSQLScalar(strcmd, dpc));
                    if (ic > 0)
                    {
                        b = true;
                    }
                    else
                    {
                        b = false;
                    }
                }
                catch
                {
                    b = false;
                    throw;
                }
            }
            return b;
        }

        public static bool IsAllUserCheck(string strCheckType, string strMainKey)
        {
            string strcmd = @"SELECT COUNT(A.BillCheckID)
FROM SYS_BillCheck A
LEFT JOIN SYS_BillCheckDetail B ON B.BillCheckID=A.BillCheckID
WHERE A.BillCode=@BillCode
AND NOT EXISTS(SELECT * FROM SYS_BillCheckRecords AA 
               WHERE AA.BillType=A.BillCode AND AA.BillID=@BillID
AND AA.CheckUser=B.CheckUser)";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillCode", strCheckType);
            dpc.AddWithValue("@BillID", strMainKey);

            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    int ic = Convert.ToInt32(broker.ExecuteSQLScalar(strcmd, dpc));
                    if (ic > 0)
                    {
                        b = true;
                    }
                    else
                    {
                        b = false;
                    }
                }
                catch
                {
                    b = false;
                    throw;
                }
            }
            return b;
        }

        public static bool IsHighVersion(string strBLLFileName, string strMainKey)
        {
            string strcmd = "";
            switch (strBLLFileName)
            {
                case "ScBomlist":
                    strcmd = @"SELECT COUNT(*) FROM SC_BomList a 
WHERE 1=1 
AND a.BomListID=@BomListID
AND a.BomListVersionNO=(SELECT MAX(aa.BomListVersionNO) 
                   FROM SC_BomList aa WHERE aa.BomListCode=a.BomListCode)";
                    break;
                default:
                    break;
            }

            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BomListID", strMainKey);

            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    int ic = Convert.ToInt32(broker.ExecuteSQLScalar(strcmd, dpc));
                    if (ic > 0)
                    {
                        b = true;
                    }
                    else
                    {
                        b = false;
                    }
                }
                catch
                {
                    b = false;
                    throw;
                }
            }
            return b;
        }

        public static bool IsUserHavePower(string strUserID, string strRoleName)
        {
            string strcmd = @"SELECT COUNT(*) FROM SYS_UserRole a
WHERE a.UserID=@UserID
AND a.RoleID IN (SELECT aa.RoleID FROM SYS_Role aa
                 WHERE aa.RoleName=@RoleName)";

            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@UserID", strUserID);
            dpc.AddWithValue("@RoleName", strRoleName);

            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    int ic = Convert.ToInt32(broker.ExecuteSQLScalar(strcmd, dpc));
                    if (ic > 0)
                    {
                        b = true;
                    }
                    else
                    {
                        b = false;
                    }
                }
                catch
                {
                    b = false;
                    throw;
                }
            }
            return b;
        }

        public static bool IsCheckBill(string strSign)
        {
            string strcmd = @"SELECT COUNT(*) FROM SYS_BillCheck a
WHERE a.BillCode=@BillCode AND a.IsCheck='1'";

            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillCode", strSign);

            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    int ic = Convert.ToInt32(broker.ExecuteSQLScalar(strcmd, dpc));
                    if (ic > 0)
                    {
                        b = true;
                    }
                    else
                    {
                        b = false;
                    }
                }
                catch
                {
                    b = false;
                    throw;
                }
            }
            return b;
        }

        public static bool IsViewBill(string strSign, string strUserID)
        {
            string strcmd = @"SELECT COUNT(*) FROM SYS_AmountView a
LEFT JOIN SYS_AmountViewDetail b on b.AmountViewID=a.AmountViewID
WHERE a.BillCode=@BillCode AND a.IsView='1'
and b.ViewUser=@ViewUser";

            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillCode", strSign);
            dpc.AddWithValue("@ViewUser", strUserID);

            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    int ic = Convert.ToInt32(broker.ExecuteSQLScalar(strcmd, dpc));
                    if (ic > 0)
                    {
                        b = true;
                    }
                    else
                    {
                        b = false;
                    }
                }
                catch
                {
                    b = false;
                    throw;
                }
            }
            return b;
        }

        public static bool IsAuditBill(string strModuleID, DataAccessBroker broker)
        {
            string strcmd = @"SELECT COUNT(*) 
FROM SYS_FormAudit a
WHERE a.ModuleID=@ModuleID AND a.IsCheck='1'";

            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@ModuleID", strModuleID);
            return ClsCommDA.ExeBoolean(strcmd, dpc, broker);
        }

        public static bool ExeBoolean(string strcmd, DataAccessParameterCollection dpc, DataAccessBroker broker)
        {
            bool b = false;
            try
            {
                int ic = Convert.ToInt32(broker.ExecuteSQLScalar(strcmd, dpc));
                if (ic > 0)
                {
                    b = true;
                }
                else
                {
                    b = false;
                }
            }
            catch
            {
                b = false;
                throw;
            }
            return b;
        }
        public static bool ExeBoolean(string strcmd, DataAccessParameterCollection dpc)
        {
            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    int ic = Convert.ToInt32(broker.ExecuteSQLScalar(strcmd, dpc));
                    if (ic > 0)
                    {
                        b = true;
                    }
                    else
                    {
                        b = false;
                    }
                }
                catch
                {
                    b = false;
                    throw;
                }
            }
            return b;
        }

        public static string ExeString(string strcmd, DataAccessParameterCollection dpc)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (null == obj)
                    {
                        return "";
                    }
                    else
                    {
                        return obj.ToString().Trim();
                    }
                }
                catch
                {
                    throw;
                }
            }
        }

        public static decimal ExeDecimal(string strcmd, DataAccessParameterCollection dpc)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    return CommonMethod.ConvertToDecimal(obj);
                }
                catch
                {
                    throw;
                }
            }
        }

        public static string ExeString(string strcmd, DataAccessParameterCollection dpc, DataAccessBroker broker)
        {
            try
            {
                object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                if (null == obj)
                {
                    return "";
                }
                else
                {
                    return obj.ToString().Trim();
                }
            }
            catch
            {
                throw;
            }
        }

        public static DataTable ExeDataTable(string strcmd, DataAccessParameterCollection dpc)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable ExeCommTable(string strcmd, DataAccessParameterCollection dpc)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    return broker.FillCommandDataSet(strcmd, dpc).Tables[0];
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable ExeDataTable(string strcmd, DataAccessParameterCollection dpc, DataAccessBroker broker)
        {
            return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
        }

        public static DataSet ExeDataSet(string strcmd, DataAccessParameterCollection dpc)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    return broker.FillSQLDataSet(strcmd, dpc);
                }
                catch
                {
                    throw;
                }
            }
        }

        public static void ExeSQL(string strcmd, DataAccessParameterCollection dpc)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.ExecuteSQL(strcmd, dpc);
                }
                catch
                {
                    throw;
                }
            }
        }

        public static void BillSaveAudit(string strTbl, string strMainKeyValue, string strModuleID, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"INSERT INTO SYS_FormAuditBill
(
	BillType,
	BillID,
	OriCheckUser,
	OrderNum
)
SELECT @BillType,@BillID,b.CheckUser,b.OrderNum
FROM SYS_FormAudit a
LEFT JOIN SYS_FormAuditDetail b ON b.FormAuditID=a.FormAuditID
WHERE a.ModuleID=@ModuleID";
            dpc.AddWithValue("@BillType", strTbl);
            dpc.AddWithValue("@BillID", strMainKeyValue);
            dpc.AddWithValue("@ModuleID", strModuleID);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static bool IsExistAuditUser(string strModule, string strUserID)
        {
            string strcmd = @"SELECT COUNT(*) FROM SYS_FormAudit A
LEFT JOIN SYS_FormAuditDetail B ON B.FormAuditID=A.FormAuditID
LEFT JOIN MD_CheckUser c ON c.CheckUser=b.CheckUser AND c.IsProxy='1'
LEFT JOIN MD_CheckUserDetail d ON d.CheckUserID=c.CheckUserID
WHERE A.IsCheck='1' AND A.ModuleID=@ModuleID
AND (B.CheckUser=@CheckUser OR d.ProxyUser=@CheckUser)";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@ModuleID", strModule);
            dpc.AddWithValue("@CheckUser", strUserID);

            return ClsCommDA.ExeBoolean(strcmd, dpc);
        }

        public static bool IsExistAudit(string strModule)
        {
            string strcmd = @"SELECT COUNT(*) FROM SYS_FormAudit A
WHERE A.IsCheck='1' AND A.ModuleID=@ModuleID";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@ModuleID", strModule);

            return ClsCommDA.ExeBoolean(strcmd, dpc);
        }

        public static string SelectFormAuditBillID(string m_strTbl, string strMainKey)
        {
            string strcmd = @"SELECT TOP 1 a.FormAuditBillID FROM SYS_FormAuditBill a
WHERE a.BillType=@BillType AND a.BillID=@BillID
ORDER BY a.OrderNum";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillType", m_strTbl);
            dpc.AddWithValue("@BillID", strMainKey);

            return ClsCommDA.ExeString(strcmd, dpc);
        }

        

        public static string SelectNextCheckUserName(string strFormAuditBillID)
        {
            string strcmd = @"SELECT TOP 1 b.UserName as NextCheckUserName FROM SYS_FormAuditBill a
LEFT JOIN SYS_User b ON b.UserID=a.OriCheckUser
WHERE a.FormAuditBillID=@FormAuditBillID";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            decimal dFormAuditBillID = CommonMethod.ConvertToDecimal(strFormAuditBillID);
            dpc.AddWithValue("@FormAuditBillID", dFormAuditBillID);

            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (null == obj)
                    {
                        return "";
                    }
                    else
                    {
                        return obj.ToString().Trim();
                    }
                }
                catch
                {
                    throw;
                }
            }
        }

        public static string SelectNextCheckUser(string strFormAuditBillID)
        {
            string strcmd = @"SELECT TOP 1 a.OriCheckUser as NextCheckUser FROM SYS_FormAuditBill a
WHERE a.FormAuditBillID=@FormAuditBillID";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            decimal dFormAuditBillID = CommonMethod.ConvertToDecimal(strFormAuditBillID);
            dpc.AddWithValue("@FormAuditBillID", dFormAuditBillID);

            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (null == obj)
                    {
                        return "";
                    }
                    else
                    {
                        return obj.ToString().Trim();
                    }
                }
                catch
                {
                    throw;
                }
            }
        }

        public static bool IsProxy(string strUserID, string strProxyer)
        {
            string strcmd = @"SELECT COUNT(*) FROM MD_CheckUser a
LEFT JOIN MD_CheckUserDetail b ON b.CheckUserID=a.CheckUserID
WHERE a.IsProxy='1' AND a.CheckUser=@CheckUser AND b.ProxyUser=@ProxyUser";

            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@CheckUser", strUserID);
            dpc.AddWithValue("@ProxyUser", strProxyer);

            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    int ic = Convert.ToInt32(broker.ExecuteSQLScalar(strcmd, dpc));
                    if (ic > 0)
                    {
                        b = true;
                    }
                    else
                    {
                        b = false;
                    }
                }
                catch
                {
                    b = false;
                    throw;
                }
            }
            return b;
        }

        public static void UpdateCheckRecord(string strFormAuditBillID, string strIsSuccess, string strFailureSeason, string strCurUserID, string strTbl, string strMainKey, string strMainKeyValue, string strCheckRes)
        {
            string strcmd = @"INSERT INTO SYS_FormAuditRecord
(
	BillType,
	BillID,
	OriCheckUser,
	OrderNum,
	CheckDate,
	IsSuccess,
	FailureSeason,
	AutalCheckUser
)
SELECT a.BillType, a.BillID, a.OriCheckUser, a.OrderNum
,GETDATE(),@IsSuccess,@FailureSeason,@AutalCheckUser
FROM SYS_FormAuditBill a 
WHERE a.FormAuditBillID=@FormAuditBillID;
DELETE FROM SYS_FormAuditBill WHERE FormAuditBillID=@FormAuditBillID";

            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            decimal dFormAuditBillID = CommonMethod.ConvertToDecimal(strFormAuditBillID);
            dpc.AddWithValue("@IsSuccess", strIsSuccess);
            dpc.AddWithValue("@FailureSeason", strFailureSeason);
            dpc.AddWithValue("@AutalCheckUser", strCurUserID);
            dpc.AddWithValue("@FormAuditBillID", dFormAuditBillID);

            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    broker.ExecuteSQL(strcmd, dpc);

                    if (strIsSuccess.Equals("0"))
                    {
                        string strtep = string.Format("UPDATE {0} set Status='{3}' where {1}='{2}'", strTbl, strMainKey, strMainKeyValue, strCheckRes);
                        broker.ExecuteSQL(strtep);
                        ClsCommDA.DelAuditBill(strTbl, strMainKeyValue, broker);
                    }
                    else if (strIsSuccess.Equals("1"))
                    {
                        string strtep = string.Format(@"IF not EXISTS(SELECT * FROM SYS_FormAuditBill a WHERE a.BillType='{0}' AND a.BillID='{2}')
BEGIN
	UPDATE {0} set Status='{3}' where {1}='{2}'
END", strTbl, strMainKey, strMainKeyValue, strCheckRes);
                        broker.ExecuteSQL(strtep, dpc);
                    }
                    
                    broker.Commit();
                }
                catch
                {
                    broker.Rollback();
                    throw;
                }
            }
        }

        public static string GetFailureSeason(string strTbl, string strCode)
        {
            string strcmd = @"SELECT TOP 1 a.FailureSeason FROM SYS_FormAuditRecord a
WHERE a.BillType=@BillType AND a.BillID=@BillID AND a.IsSuccess='0'
ORDER BY a.OrderNum DESC";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillType", strTbl);
            dpc.AddWithValue("@BillID", strCode);

            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (null == obj)
                    {
                        return "";
                    }
                    else
                    {
                        return obj.ToString().Trim();
                    }
                }
                catch
                {
                    throw;
                }
            }
        }

        public static bool IsFailure(string strTbl, string strCode)
        {
            string strcmd = @"SELECT COUNT(*) FROM SYS_FormAuditRecord a
WHERE a.BillType=@BillType AND a.BillID=@BillID AND a.IsSuccess='0'";

            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillType", strTbl);
            dpc.AddWithValue("@BillID", strCode);

            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    int ic = Convert.ToInt32(broker.ExecuteSQLScalar(strcmd, dpc));
                    if (ic > 0)
                    {
                        b = true;
                    }
                    else
                    {
                        b = false;
                    }
                }
                catch
                {
                    b = false;
                    throw;
                }
            }
            return b;
        }

        public static string SelectUserByCheckUser(string strCheckUser)
        {
            string strcmd = @"SELECT a.UserName FROM SYS_User a WHERE a.UserID=@UserID";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@UserID", strCheckUser);

            return ClsCommDA.ExeString(strcmd, dpc);
        }

        public static bool IsCheckUser(string m_strTbl, string strStockID, string strFormAuditBillID, string strCurUser)
        {
            string strcmd = @"SELECT a.OriCheckUser FROM SYS_FormAuditBill a
WHERE a.BillType=@BillType AND a.BillID=@BillID
AND a.FormAuditBillID=@FormAuditBillID
AND a.OriCheckUser=@strCurUser
AND not EXISTS(SELECT * FROM MD_CheckUser aa WHERE aa.CheckUser=a.OriCheckUser 
	AND aa.IsProxy='1')
UNION ALL 
SELECT c.ProxyUser FROM SYS_FormAuditBill a
LEFT JOIN MD_CheckUser b ON b.CheckUser=a.OriCheckUser
LEFT JOIN MD_CheckUserDetail c ON c.CheckUserID=b.CheckUserID
WHERE a.BillType=@BillType AND a.BillID=@BillID
AND a.FormAuditBillID=@FormAuditBillID
AND c.ProxyUser=@strCurUser
AND EXISTS(SELECT * FROM MD_CheckUser aa WHERE aa.CheckUser=a.OriCheckUser 
	AND aa.IsProxy='1')";

            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            decimal dFormAuditBillID = CommonMethod.ConvertToDecimal(strFormAuditBillID);
            dpc.AddWithValue("@BillType", m_strTbl);
            dpc.AddWithValue("@BillID", strStockID);
            dpc.AddWithValue("@FormAuditBillID", dFormAuditBillID);
            dpc.AddWithValue("@strCurUser", strCurUser);

            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (obj == null || string.IsNullOrEmpty(obj.ToString().Trim()))
                    {
                        b = false;
                    }
                    else
                    {
                        b = true;
                    }
                }
                catch
                {
                    b = false;
                    throw;
                }
            }
            return b;
        }

        public static void DelFile(string strFormType, string strFormID, DataAccessBroker broker)
        {
            string strcmd = "DELETE FROM MD_Attachment WHERE FormType=@FormType AND FormID=@FormID";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@FormType", strFormType);
            dpc.AddWithValue("@FormID", strFormID);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static void UpdateProxy(string strCheckUser, string strIsProxy)
        {
            string strcmd = @"UPDATE MD_CheckUser SET IsProxy=@IsProxy where CheckUser=@CheckUser;
            INSERT INTO MD_CheckUserRecord
(
	CheckUser,
	IsProxy,
	OperateDate
)
VALUES
(
	@CheckUser,
	@IsProxy,
	GETDATE()
)";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@IsProxy", strIsProxy);
            dpc.AddWithValue("@CheckUser", strCheckUser);
            ClsCommDA.ExeSQL(strcmd, dpc);
        }

        public static string GetProxy(string strCheckUser)
        {
            string strcmd = "SELECT IsProxy FROM MD_CheckUser where CheckUser=@CheckUser";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@CheckUser", strCheckUser);
            return ClsCommDA.ExeString(strcmd, dpc);
        }

        public static bool NoCheck(string strTbl, string strMainKeyValue)
        {
            string strcmd = "SELECT * FROM SYS_FormAuditBill a WHERE a.BillType=@BillType AND a.BillID=@BillID";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillType", strTbl);
            dpc.AddWithValue("@BillID", strMainKeyValue);
            return !ClsCommDA.ExeBoolean(strcmd, dpc);
        }

        public static bool NoCheck(string strTbl, string strMainKeyValue, DataAccessBroker broker)
        {
            string strcmd = "SELECT * FROM SYS_FormAuditBill a WHERE a.BillType=@BillType AND a.BillID=@BillID";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillType", strTbl);
            dpc.AddWithValue("@BillID", strMainKeyValue);
            return !ClsCommDA.ExeBoolean(strcmd, dpc, broker);
        }

        public static bool IsCheck(string strTbl, string strMainKey, string strMainValue)
        {
            string strcmd = string.Format(@"select COUNT(*) from {0} where {1}=@{2} and Status='1'", strTbl, strMainKey, strMainKey);
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@" + strMainKey, strMainValue);

            return ExeBoolean(strcmd, dpc);
        }

        public static DataSet GetMainSet(string strUserID)
        {
            string strcmd = @"SELECT COUNT(*) AS TotalBillNum FROM V_FormAuditBill where CheckUser=@CheckUser;
SELECT a.BillType,COUNT(*) AS BillNum 
FROM V_FormAuditBill a
where a.CheckUser=@CheckUser
GROUP BY a.BillType;";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@CheckUser", strUserID);
            return ExeDataSet(strcmd, dpc);
        }

        public static DataTable GetMainBill(string strBillType)
        {
            string strcmd = @"SELECT * FROM V_FormAuditBill a
WHERE a.BillType=@BillType";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillType", strBillType);
            return ExeDataTable(strcmd, dpc);
        }

        public static DataTable SelectCheck(string strTbl, string strCode)
        {
            string strcmd = @"SELECT a.*,b.UserName AS OriCheckUserName
,c.UserName AS AutalCheckUserName
FROM SYS_FormAuditRecord a
LEFT JOIN SYS_User b ON b.UserID=a.OriCheckUser
LEFT JOIN SYS_User c ON c.UserID=a.AutalCheckUser
WHERE a.BillType=@BillType AND a.BillID=@BillID
ORDER BY a.CheckDate";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillType", strTbl);
            dpc.AddWithValue("@BillID", strCode);
            return ExeDataTable(strcmd, dpc);
        }

        public static DataTable GetMainWorkExpire(string strUserID)
        {
            string strcmd = @"SELECT * FROM V_WorkExpireDateMainMetion a
where a.CreateUserID=@CreateUserID";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@CreateUserID", strUserID);
            return ExeDataTable(strcmd, dpc);
        }

        public static void ExeSQL(string strcmd, DataAccessParameterCollection dpc, DataAccessBroker broker)
        {
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static void DelStockManagePro(string strTbl, string strCode, DataAccessBroker broker)
        {
            string strcmd = @"DELETE FROM ST_StockManagePro WHERE BillType=@BillType AND BillCode=@BillCode";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillType", strTbl);
            dpc.AddWithValue("@BillCode", strCode);
            ExeSQL(strcmd, dpc, broker);
        }

        public static void DelStockManageMat(string strTbl, string strCode, DataAccessBroker broker)
        {
            string strcmd = @"DELETE FROM ST_StockManageMat WHERE BillType=@BillType AND BillCode=@BillCode";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillType", strTbl);
            dpc.AddWithValue("@BillCode", strCode);
            ExeSQL(strcmd, dpc, broker);
        }

        public static void DelStockManageMatDetail(string strTbl, string strCode, DataAccessBroker broker)
        {
            string strcmd = @"DELETE FROM ST_StockManageMat WHERE BillType=@BillType AND BillDetailCode=@BillDetailCode";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillType", strTbl);
            dpc.AddWithValue("@BillDetailCode", strCode);
            ExeSQL(strcmd, dpc, broker);
        }

        public static void DelStockManageMat(string strTbl, string strDelSign, string strCode, DataAccessBroker broker)
        {
            string strcmd = @"DELETE FROM ST_StockManageMat WHERE BillType=@BillType and DelSign=@DelSign AND BillCode=@BillCode";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@BillType", strTbl);
            dpc.AddWithValue("@DelSign", strDelSign);
            dpc.AddWithValue("@BillCode", strCode);
            ExeSQL(strcmd, dpc, broker);
        }

        public static DataTable SelectPreBig(string strddlBigTypeID,
                                                      int pageIndex,
                                                      int pageSize,
                                                      out int count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"SELECT c.*,'1' as PreType
FROM MD_BigType c
WHERE c.[Status]='1'";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                if (!string.IsNullOrEmpty(strddlBigTypeID))
                {
                    strcmd += " and c.BigTypeID = @BigTypeID";
                    dpc.AddWithValue("@BigTypeID", strddlBigTypeID);
                }

                strcmd += " ORDER BY c.ModifyDate DESC";

                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable SelectPreSmall(string strddlBigTypeID, string strddlSmallTypeID,
                                                     int pageIndex,
                                                     int pageSize,
                                                     out int count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"SELECT b.*, c.BigTypeName,'2' as PreType
FROM MD_SmallType b
LEFT JOIN MD_BigType c ON c.BigTypeID=b.BigTypeID
WHERE b.[Status]='1'";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                if (!string.IsNullOrEmpty(strddlBigTypeID))
                {
                    strcmd += " and b.BigTypeID = @BigTypeID";
                    dpc.AddWithValue("@BigTypeID", strddlBigTypeID);
                }

                if (!string.IsNullOrEmpty(strddlSmallTypeID))
                {
                    strcmd += " and b.SmallTypeID = @SmallTypeID";
                    dpc.AddWithValue("@SmallTypeID", strddlSmallTypeID);
                }

                strcmd += " ORDER BY b.ModifyDate DESC";

                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable SelStaAluminiumList(string strtxtItemName, string strMatCode, string strMatName)
        {
            string strcmd = @"SELECT * FROM V_StaAluminium a
WHERE 1=1";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(strtxtItemName))
            {
                strcmd += " and a.ItemName like @ItemName";
                dpc.AddWithValue("@ItemName", "%" + strtxtItemName + "%");
            }
            if (!string.IsNullOrEmpty(strMatCode))
            {
                strcmd += " and a.BaseAluminiumID like @BaseAluminiumID";
                dpc.AddWithValue("@BaseAluminiumID", "%" + strMatCode + "%");
            }
            if (!string.IsNullOrEmpty(strMatName))
            {
                strcmd += " and a.BaseAluminiumName like @BaseAluminiumName";
                dpc.AddWithValue("@BaseAluminiumName", "%" + strMatName + "%");
            }
            strcmd += " ORDER BY a.ItemName,a.lx,a.BaseAluminiumID";
            return ClsCommDA.ExeDataTable(strcmd, dpc);
        }

        public static DataTable SelStaGlassList(string strtxtItemName, string strMatCode)
        {
            string strcmd = @"SELECT * FROM V_StaGlass a
WHERE 1=1";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(strtxtItemName))
            {
                strcmd += " and a.ItemName like @ItemName";
                dpc.AddWithValue("@ItemName", "%" + strtxtItemName + "%");
            }
            if (!string.IsNullOrEmpty(strMatCode))
            {
                strcmd += " and a.BaseGlassID like @BaseGlassID";
                dpc.AddWithValue("@BaseGlassID", "%" + strMatCode + "%");
            }
            strcmd += " ORDER BY a.ItemName,a.lx,a.BaseGlassID";
            return ClsCommDA.ExeDataTable(strcmd, dpc);
        }

        public static DataTable SelStaOtherList(string strtxtItemName, string strMatCode, string strMatName)
        {
            string strcmd = @"SELECT * FROM V_StaOther a
WHERE 1=1";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(strtxtItemName))
            {
                strcmd += " and a.ItemName like @ItemName";
                dpc.AddWithValue("@ItemName", "%" + strtxtItemName + "%");
            }
            if (!string.IsNullOrEmpty(strMatCode))
            {
                strcmd += " and a.BaseOtherID like @BaseOtherID";
                dpc.AddWithValue("@BaseOtherID", "%" + strMatCode + "%");
            }
            if (!string.IsNullOrEmpty(strMatName))
            {
                strcmd += " and a.BaseOtherName like @BaseOtherName";
                dpc.AddWithValue("@BaseOtherName", "%" + strMatName + "%");
            }
            strcmd += " ORDER BY a.ItemName,a.lx,a.BaseOtherID";
            return ClsCommDA.ExeDataTable(strcmd, dpc);
        }

        public static DataTable SelStaSteelList(string strtxtItemName, string strMatCode, string strMatName)
        {
            string strcmd = @"SELECT * FROM V_StaSteel a
WHERE 1=1";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(strtxtItemName))
            {
                strcmd += " and a.ItemName like @ItemName";
                dpc.AddWithValue("@ItemName", "%" + strtxtItemName + "%");
            }
            if (!string.IsNullOrEmpty(strMatCode))
            {
                strcmd += " and a.BaseSteelID like @BaseSteelID";
                dpc.AddWithValue("@BaseSteelID", "%" + strMatCode + "%");
            }
            if (!string.IsNullOrEmpty(strMatName))
            {
                strcmd += " and a.BaseSteelName like @BaseSteelName";
                dpc.AddWithValue("@BaseSteelName", "%" + strMatName + "%");
            }
            strcmd += " ORDER BY a.ItemName,a.lx,a.BaseSteelID";
            return ClsCommDA.ExeDataTable(strcmd, dpc);
        }

        public static DataTable SelStaStoneList(string strtxtItemName, string strMatCode, string strMatName)
        {
            string strcmd = @"SELECT * FROM V_StaStone a
WHERE 1=1";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(strtxtItemName))
            {
                strcmd += " and a.ItemName like @ItemName";
                dpc.AddWithValue("@ItemName", "%" + strtxtItemName + "%");
            }
            if (!string.IsNullOrEmpty(strMatCode))
            {
                strcmd += " and a.BaseStoneID like @BaseStoneID";
                dpc.AddWithValue("@BaseStoneID", "%" + strMatCode + "%");
            }
            if (!string.IsNullOrEmpty(strMatName))
            {
                strcmd += " and a.BaseStoneName like @BaseStoneName";
                dpc.AddWithValue("@BaseStoneName", "%" + strMatName + "%");
            }
            strcmd += " ORDER BY a.ItemName,a.lx,a.BaseStoneID";
            return ClsCommDA.ExeDataTable(strcmd, dpc);
        }

        public static DataTable SelStaAluSingleList(string strtxtItemName, string strtxtBaseAluSingleID, string strtxtPanelID)
        {
            string strcmd = @"SELECT * FROM V_StaAluSingle a
WHERE 1=1";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(strtxtItemName))
            {
                strcmd += " and a.ItemName like @ItemName";
                dpc.AddWithValue("@ItemName", "%" + strtxtItemName + "%");
            }
            if (!string.IsNullOrEmpty(strtxtBaseAluSingleID))
            {
                strcmd += " and a.BaseAluSingleID like @BaseAluSingleID";
                dpc.AddWithValue("@BaseAluSingleID", "%" + strtxtBaseAluSingleID + "%");
            }
            if (!string.IsNullOrEmpty(strtxtPanelID))
            {
                strcmd += " and a.PanelID like @PanelID";
                dpc.AddWithValue("@PanelID", "%" + strtxtPanelID + "%");
            }
            strcmd += " ORDER BY a.ItemName,a.lx,a.BaseAluSingleID";
            return ClsCommDA.ExeDataTable(strcmd, dpc);
        }

        public static DataTable SelStaItemMgrList(string strtxtFeeItemName, string strtxtItemName, string strddllx, string strtxtBillCode)
        {
            string strcmd = @"SELECT a.FeeItemID,a.ItemID,b.FeeItemName,c.ItemName,a.Amount,d.lxName, d.BillCode, d.PreAmount,d.lx
FROM BS_FeeManger a
LEFT JOIN MD_FeeItem b ON b.FeeItemID=a.FeeItemID
LEFT JOIN MD_Item c ON c.ItemID=a.ItemID
LEFT JOIN V_ItemMagMat d ON d.ItemID= a.ItemID
WHERE 1=1";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(strtxtFeeItemName))
            {
                strcmd += " and b.FeeItemName like @FeeItemName";
                dpc.AddWithValue("@FeeItemName", "%" + strtxtFeeItemName + "%");
            }
            if (!string.IsNullOrEmpty(strtxtItemName))
            {
                strcmd += " and c.ItemName like @ItemName";
                dpc.AddWithValue("@ItemName", "%" + strtxtItemName + "%");
            }
            if (!string.IsNullOrEmpty(strddllx))
            {
                strcmd += " and d.lx = @lx";
                dpc.AddWithValue("@lx", strddllx);
            }
            if (!string.IsNullOrEmpty(strtxtBillCode))
            {
                strcmd += " and d.BillCode like @BillCode";
                dpc.AddWithValue("@BillCode", "%" + strtxtBillCode + "%");
            }
            strcmd += " ORDER BY b.FeeItemName,c.ItemName,d.lxName,d.BillCode";
            return ClsCommDA.ExeDataTable(strcmd, dpc);
        }

        public static DataTable SelProStockList(string strtxtProductName, string strtxtSpecifications)
        {
            string strcmd = @"SELECT b.ProductID, b.ProductName, b.Specifications, b.Unit,a.StockNum
FROM V_StockManagePro a
LEFT JOIN MD_Product b ON b.ProductID=a.ProductID
WHERE 1=1";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(strtxtProductName))
            {
                strcmd += " and b.ProductName like @ProductName";
                dpc.AddWithValue("@ProductName", "%" + strtxtProductName + "%");
            }
            if (!string.IsNullOrEmpty(strtxtSpecifications))
            {
                strcmd += " and b.Specifications like @Specifications";
                dpc.AddWithValue("@Specifications", "%" + strtxtSpecifications + "%");
            }
            strcmd += " ORDER BY b.ProductName";
            return ClsCommDA.ExeDataTable(strcmd, dpc);
        }

        public static DataTable SelMatStockList(string strddlBigTypeID, string strddlSmallTypeID, string strddlMatType, string strtxtMatID, string strtxtMatName, string strtxtSpecifications)
        {
            string strcmd = @"SELECT b.MatType, b.MatID, b.MatName, b.StoneType, b.Thickness, b.Specifications,
       b.Width, b.Height, b.Configuration, b.SmallTypeID, b.SmallTypeName,
       b.BigTypeID, b.BigTypeName, b.FaceDeal, b.LinearDensity, b.MaterialQuality,
       b.MetreHeavy, b.EdgeDeal, b.PanelID, b.Clr, b.[Status], b.CreateUserID,
       b.CreateUserName, b.CreateDate, b.ModifyUserID, b.ModifyUserName,
       b.ModifyDate,a.BranchNum, a.MatWeight, a.Amount
FROM V_StockManageMatNoStock a
LEFT JOIN V_Mat b ON b.MatType=a.MatType AND b.MatID=a.MatID
WHERE 1=1";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(strddlBigTypeID))
            {
                strcmd += " and b.BigTypeID = @BigTypeID";
                dpc.AddWithValue("@BigTypeID", strddlBigTypeID);
            }
            if (!string.IsNullOrEmpty(strddlSmallTypeID))
            {
                strcmd += " and b.SmallTypeID = @SmallTypeID";
                dpc.AddWithValue("@SmallTypeID", strddlSmallTypeID);
            }
            if (!string.IsNullOrEmpty(strddlMatType))
            {
                strcmd += " and b.MatType = @MatType";
                dpc.AddWithValue("@MatType", strddlMatType);
            }
            if (!string.IsNullOrEmpty(strtxtMatID))
            {
                strcmd += " and b.MatID like @MatID";
                dpc.AddWithValue("@MatID", "%" + strtxtMatID + "%");
            }
            if (!string.IsNullOrEmpty(strtxtMatName))
            {
                strcmd += " and b.MatName like @MatName";
                dpc.AddWithValue("@MatName", "%" + strtxtMatName + "%");
            }
            if (!string.IsNullOrEmpty(strtxtSpecifications))
            {
                strcmd += " and b.Specifications like @Specifications";
                dpc.AddWithValue("@Specifications", "%" + strtxtSpecifications + "%");
            }
            strcmd += " ORDER BY b.MatType, b.MatID";
            return ClsCommDA.ExeDataTable(strcmd, dpc);
        }

        public static decimal GetStockNum(string strMaterialID)
        {
            //string strcmd = @"SELECT a.StockNum FROM V_StockManageMatNoStock a WHERE 1=1 AND a.MaterialID=@MaterialID";
            //修改时间2016-01-05
            string strcmd = @"SELECT a.StockNum FROM V_StockManageMatNoStock a WHERE 1=1 AND a.MaterialID=@MaterialID and (UseDepartID is not null or UseDepartID <> '')";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@MaterialID", strMaterialID);
            string str = ClsCommDA.ExeString(strcmd, dpc);

            decimal dStockNum = CommonMethod.ConvertToDecimal(str);
            return dStockNum;
        }

        public static decimal GetStockNum2(string strMaterialID)
        {
            //string strcmd = @"SELECT a.StockNum2 FROM V_StockManageMatNoStock a WHERE 1=1 AND a.MaterialID=@MaterialID";
            //修改时间2016-01-05
            string strcmd = @"SELECT a.StockNum2 FROM V_StockManageMatNoStock a WHERE 1=1 AND a.MaterialID=@MaterialID and (UseDepartID is not null or UseDepartID <> '')";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@MaterialID", strMaterialID);
            string str = ClsCommDA.ExeString(strcmd, dpc);

            decimal dStockNum = CommonMethod.ConvertToDecimal(str);
            return dStockNum;
        }

        public static decimal GetStockNumPro(string strProductID, DataAccessBroker broker)
        {
            string strcmd = @"SELECT a.StockNum FROM V_StockManageProNoStock a WHERE 1=1 AND a.ProductID=@ProductID";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@ProductID", strProductID);
            string str = ClsCommDA.ExeString(strcmd, dpc, broker);

            decimal dStockNum = CommonMethod.ConvertToDecimal(str);
            return dStockNum;
        }

        public static string GetBillCode(string strTbl, string strMainKey, string strBillPre, int iLen)
        {
            string strtep = "";
            for (int i = 0; i < iLen; i++)
            {
                strtep += "[0-9]";
            }
            int iMaxLen = strBillPre.Length + iLen;
            string strcmd = string.Format(@"SELECT max(convert(int,SUBSTRING(a.{1},LEN(a.{1})-{5},{4}))) FROM {0} a WHERE a.{1} LIKE @strCode and a.{1} LIKE '%{2}' and len(a.{1})={3}", strTbl, strMainKey, strtep, iMaxLen, iLen, iLen - 1);
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@strCode", strBillPre + "%");
            int ic = CommonMethod.ConvertToInt(ClsCommDA.ExeString(strcmd, dpc)) + 1;
            return string.Format("{0}{1}", strBillPre, ic.ToString().PadLeft(iLen, '0'));
        }
        public static string SelectNextCheckUserPro(string strFormAuditBillID, string strCreateUserID, string strModuleID)
        {
            string strcmd = @"GetNextCheckUserNew";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@FormAuditBillID", strFormAuditBillID);
            dpc.AddWithValue("@CreateUserID", strCreateUserID);
            dpc.AddWithValue("@BillType", strModuleID);

            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    object obj = broker.ExecuteCommandScalar(strcmd, dpc);
                    if (null == obj)
                    {
                        return "";
                    }
                    else
                    {
                        return obj.ToString().Trim();
                    }
                }
                catch
                {
                    throw;
                }
            }
        }
        public static bool BillSaveAudit(string strTbl, string strMainKeyValue, string strModuleID, string strUserID, DataAccessBroker broker)
        {
            //DataSet ds = SelectAudit(strModuleID, strUserID, broker);
            //DataTable dt = null;
            //if (ds.Tables[0] == null || ds.Tables[0].Rows.Count < 1)
            //{
            //    dt = ds.Tables[1];
            //}
            //else
            //{
            //    dt = ds.Tables[0];
            //}
            DataTable dt = SelectAudit(strModuleID);
            if (dt == null || dt.Rows.Count < 1)
            {
                return false;
            }
            List<SysFormauditbillEntity> lstent = new List<SysFormauditbillEntity>();
            foreach (DataRow item in dt.Rows)
            {
                SysFormauditbillEntity ent = new SysFormauditbillEntity();
                ent.BillType = strTbl;
                ent.BillID = strMainKeyValue;
                ent.OriCheckUser = CommonMethod.GetString(item["CheckUser"]);
                ent.OrderNum = CommonMethod.ConvertToInt(item["OrderNum"]);
                lstent.Add(ent);
            }

            foreach (SysFormauditbillEntity item in lstent)
            {
                DataAccessCommon.Insert(item, true, broker);
            }
            return true;
        }
        public static DataTable SelectAudit(string strModuleID)
        {
            string strcmd = @"SELECT c.UserName AS CheckUserName,b.OrderNum,b.CheckUser
FROM SYS_FormAudit a
LEFT JOIN SYS_FormAuditDetail b ON b.FormAuditID=a.FormAuditID
LEFT JOIN SYS_User c ON c.UserID=b.CheckUser
WHERE a.IsCheck='1' AND a.ModuleID=@ModuleID
ORDER BY b.OrderNum";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@ModuleID", strModuleID);
            return ExeDataTable(strcmd, dpc);
        }

        public static void UpdateIsCheck(string strTbl, string strMainKey, string strMainKeyValue, string IsCheck, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string[] arr = strMainKeyValue.Split(',');
            string strcmd = "";
            foreach (string str in arr) 
            {
                strcmd += @"UPDATE " + strTbl + " SET IsCheck = '" + IsCheck + "' WHERE " + strMainKey + " = " + str.Trim() + ";";
            }
            broker.ExecuteSQL(strcmd, dpc);
        }
    }
}
